use rent;
create table Region (
    regionId int primary key not null,
    regionName varchar(1000));
    grant select,insert,update, delete on Region to Roxana;
create table City (
    cityId int primary key not null,
    cityName varchar(1000),
    regionID int not null, 
    FOREIGN KEY (regionID) REFERENCES Region(regionID));
 grant select,insert,update, delete on City to Roxana;
create table Branch (
    branchNo int primary key not null,
    branchName varchar(1000),
    branchAddress varchar(1000),
    cityID int not null, 
    postcode varchar(6),
    telNo numeric (11) ,
    FOREIGN KEY (cityID) REFERENCES City(cityID));
 grant select,insert,update, delete on Branch to Roxana;
	alter table Branch
	alter column postcode varchar(10);
 create table Staff (
    staffID int primary key not null,
    staffName varchar(1000),
    staffSalary float,
    staffPosition varchar(100),
    branchID int not null, 
    FOREIGN KEY (branchID) REFERENCES Branch(branchNo));
  grant select,insert,update, delete on Staff to Roxana;
  create table Client (
    clientId int primary key not null,
    clientName varchar(100),
    telephoneNumber numeric(11),
    email varchar(100),
    preAccType varchar(100),
    maxRent float,
    staffID int not null,
    branchID int not null, 
    FOREIGN KEY (staffID) REFERENCES Staff(staffID),
    FOREIGN KEY (branchID) REFERENCES Branch(branchNo));
	alter table Client add check( maxRent>=1000);

  grant select,insert,update, delete on Client to Roxana;
   create table Owner (
    ownerID int primary key not null,
    ownerName varchar(100),
    ownerAddress varchar(100),
    ownerPostcode  varchar(100),
    telephoneNumber numeric (11),
    owneremail varchar(100));
    grant select,insert,update, delete on Owner to Roxana;
     create table Property (
    propertyID int primary key not null,
    propertyAddress varchar(100),
    propertyCityID int not null,
    propertyPostcode  varchar(100),
    propertyType varchar(100),
    noofrooms int,
    monthlyRent float,
    ownerID int not null,
    FOREIGN KEY (propertyCityID) REFERENCES City(cityID),
    FOREIGN KEY (ownerID) REFERENCES Owner(ownerID));
    grant select,insert,update, delete on Property to Roxana;
    create table Lease (
    leaseNumber int primary key not null,
    clientID int not null,
    ownerID int not null,
    propertyID int not null,
    methodofpay varchar(100),
    depositPaidStatus varchar(1),
    startDate date,
    endDate date,
    FOREIGN KEY (clientID) REFERENCES Client(clientID),
    FOREIGN KEY (ownerID) REFERENCES Owner(ownerID),
    FOREIGN KEY (propertyID) REFERENCES Property(propertyID));
	grant select,insert,update, delete on Lease to Roxana;